-- Migration: Add X Layer tables
-- Created: 2024-01-17

-- X Layer tokens table
CREATE TABLE IF NOT EXISTS xlayer_tokens (
    id VARCHAR(255) PRIMARY KEY,
    launch_id INTEGER DEFAULT 0,
    token_address VARCHAR(42) DEFAULT '',
    creator VARCHAR(42) NOT NULL,
    name VARCHAR(255) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    website VARCHAR(255),
    telegram VARCHAR(255),
    twitter VARCHAR(255),
    image_url VARCHAR(500),
    target_amount VARCHAR(50) NOT NULL, -- ETH amount as string
    raised_amount VARCHAR(50) DEFAULT '0',
    token_price VARCHAR(50) NOT NULL, -- ETH per token as string
    created_at INTEGER NOT NULL,
    deadline INTEGER NOT NULL,
    launched BOOLEAN DEFAULT FALSE,
    cancelled BOOLEAN DEFAULT FALSE,
    current_price VARCHAR(50), -- Current market price
    price_change_24h DECIMAL(10,4), -- 24h price change percentage
    volume_24h VARCHAR(50), -- 24h trading volume
    market_cap VARCHAR(50), -- Current market cap
    holders INTEGER DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- X Layer contributions table
CREATE TABLE IF NOT EXISTS xlayer_contributions (
    id VARCHAR(255) PRIMARY KEY,
    launch_id INTEGER NOT NULL,
    contributor VARCHAR(42) NOT NULL,
    amount VARCHAR(50) NOT NULL, -- ETH amount contributed
    tokens_received VARCHAR(50) NOT NULL, -- Tokens received
    timestamp INTEGER NOT NULL,
    tx_hash VARCHAR(66) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- X Layer price history table
CREATE TABLE IF NOT EXISTS xlayer_price_history (
    id SERIAL PRIMARY KEY,
    token_id VARCHAR(255) NOT NULL,
    price VARCHAR(50) NOT NULL,
    volume VARCHAR(50) DEFAULT '0',
    timestamp INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (token_id) REFERENCES xlayer_tokens(id) ON DELETE CASCADE
);

-- X Layer token holders table
CREATE TABLE IF NOT EXISTS xlayer_token_holders (
    id SERIAL PRIMARY KEY,
    token_id VARCHAR(255) NOT NULL,
    holder_address VARCHAR(42) NOT NULL,
    balance VARCHAR(50) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (token_id) REFERENCES xlayer_tokens(id) ON DELETE CASCADE,
    UNIQUE(token_id, holder_address)
);

-- Indexes for better performance
CREATE INDEX IF NOT EXISTS idx_xlayer_tokens_creator ON xlayer_tokens(creator);
CREATE INDEX IF NOT EXISTS idx_xlayer_tokens_created_at ON xlayer_tokens(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_xlayer_tokens_launched ON xlayer_tokens(launched);
CREATE INDEX IF NOT EXISTS idx_xlayer_tokens_deadline ON xlayer_tokens(deadline);
CREATE INDEX IF NOT EXISTS idx_xlayer_tokens_search ON xlayer_tokens USING gin(to_tsvector('english', name || ' ' || symbol || ' ' || description));

CREATE INDEX IF NOT EXISTS idx_xlayer_contributions_launch_id ON xlayer_contributions(launch_id);
CREATE INDEX IF NOT EXISTS idx_xlayer_contributions_contributor ON xlayer_contributions(contributor);
CREATE INDEX IF NOT EXISTS idx_xlayer_contributions_timestamp ON xlayer_contributions(timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_xlayer_price_history_token_id ON xlayer_price_history(token_id);
CREATE INDEX IF NOT EXISTS idx_xlayer_price_history_timestamp ON xlayer_price_history(timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_xlayer_token_holders_token_id ON xlayer_token_holders(token_id);
CREATE INDEX IF NOT EXISTS idx_xlayer_token_holders_address ON xlayer_token_holders(holder_address);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_xlayer_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_xlayer_tokens_updated_at BEFORE UPDATE ON xlayer_tokens FOR EACH ROW EXECUTE PROCEDURE update_xlayer_updated_at_column();
CREATE TRIGGER update_xlayer_token_holders_updated_at BEFORE UPDATE ON xlayer_token_holders FOR EACH ROW EXECUTE PROCEDURE update_xlayer_updated_at_column();